''' 

''' Calls custom stored procedure.
''' 
Public Sub CallCustomStoredProcedureToAdd()
	'' Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters.
	'' Each database type has different parameter type.
	'' Please change them accordingly to fit your application's logic.
	'' SQL Server: 
	''	Parameter type: System.Data.SqlDbType
	'' Oracle:
	''	Parameter type: System.Data.OracleClient.OracleType
	'' MySql:
	'' 	Parameter type: MySql.Data.MySqlClient.MySqlDbType
	
	Dim firstParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType
	firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeFirstName", "John", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)
	
	'' For Oracle: use parameter type System.Data.OracleClient.OracleType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeFirstName", "John", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)
	
	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeFirstName", "John", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)


	Dim secondParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType
	secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)
	
	'' For Oracle: use parameter type System.Data.OracleClient.OracleType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)    
	
	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType	
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)    


	' Handle the case where the primary key column is of type uniqueidentifier
	' with a default value of newid().  In this case the value may be set in
	' the stored procedure so we'll need to get the value back via  
	' an output parameter.
	
	Dim thirdParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_OutputParameter", Nothing, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Output) 	
  '' NOTE: if this parameter has a string type such as varchar, nvarchar, string, etc. you have to set its size:
  '' thirdParameter.Size = 10
  
	'' Step 2: Add the configured parameters to an array list.
	Dim parameterList(2) As BaseClasses.Data.StoredProcedureParameter
	parameterList(0) = firstParameter
	parameterList(1) = secondParameter
	parameterList(2) = thirdParameter	

	Dim myStoredProcedure As BaseClasses.Data.StoredProcedure = Nothing

	'' "DatabaseNorthwind1" is a connection string obtained from Web.config 
	'' located in application's root directory.

	'' Step 3: Connect to the stored procedure.
	myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList)    
    

	'' Step 4: Run the stored procedure to insert a new record using the specified values.    
	'' RunNonQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
	'' Use RunQuery or RunNonQuery.  RunQuery is used when a set of records is being returned.  RunNonQuery is used when one or more values are returned through output parameters.
	If (myStoredProcedure.RunNonQuery()) Then

		' Get new primary key value if 
		' value for primary key column
		' will be set in the stored procedure.
		Dim outputParameter As System.Data.IDataParameter
		For Each outputParameter In myStoredProcedure.OutputParameters
		    Dim primaryKeyValue As Object = outputParameter.Value
		Next
	Else
		' You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
		' To raise the exception:
		
		' SET NOCOUNT ON;
	    	' RAISERROR (N'My custom error message goes here', 11, 1)
	
		' IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered  
		' a warning, and no exception is raised. The severity of the error must be between 11 and 20
		' for an exception to be thrown.
	
		' Once the exception is raised, you can look at:
		' myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.
	End If   
End Sub